Skip Headers

Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 15 Implementing Application Context and Fine-Grained Access Control

Application context can be used with fine-grained access control as part of Virtual Private Database (VPD) or by itself. Used alone, it enables application developers to define, set, and access application attributes by serving as a data cache. Such usage removes the repeated overhead of querying the database each time access to application attributes is needed.

This chapter discusses how to implement application context and fine-grained access control using the following sections:

- About Using Application Context

- Using Secure Session-Based Application Context

- Examples: Secure Application Context Within a Fine-Grained Access Control Function

- Initializing Secure Application Context Externally

- Initializing Secure Application Context Globally

- Using Client Session-Based Application Context

- How to Use Global Application Context

- How Fine-Grained Access Control Works

- How to Establish Policy Groups

- How to Add a Policy to a Table, View, or Synonym

- How to Check for Policies Applied to a SQL Statement

- Users Exempt from VPD Policies

- Automatic Reparse

- VPD Policies and Flashback Query

## 15.1 About Using Application Context

Application context can be useful for the following purposes:

- Enforcing fine-grained access control

- Preserving user identity across multitier environments

- Serving as a secure data cache for attributes needed by an application for fine-grained auditing or for use in PL/SQL conditional statements or loops

  This cache saves the repeated overhead of querying the database each time such attributes are needed.

- Serving as a holding area for attribute-value pairs that an application can define, modify, and access

Three types of application context are defined, differentiated by where the context data is stored and by how updates can be done:

- Secure session-based application contexts, for which data is stored in the database user session (UGA) in a namespace specified by an SQL command (`CREATE CONTEXT`).

  Example: `CREATE CONTEXT hr USING scott.package1`;

  Data in this secure, session-private data cache can only be set by the PL/SQL package in that command. In this example, only `scott.package1` can alter data in the `hr` namespace.

  Namespaces created in this way are maintained using the existing PL/SQL packages available in the `DBMS_SESSION` package.

  Session-based application contexts can be initialized either externally or globally, and either method stores the context information in the user session.

  - External initialization can come from an OCI interface, a job queue process, or a connected user database link.

  - Global initialization uses attributes and values from a centralized location, such as an LDAP directory.

  See Also: Initializing Secure Application Context Externally Initializing Secure Application Context Globally

  Note: The `init.ora` `parameter _session_context_size` limits the total number of (namespace, attribute) pairs used by all application contexts in the user session UGA. This limit includes the client namespace `CLIENTCONTEXT`, but excludes globally accessed context, which uses memory from the shared pool. Users can change the value `for _session_context_size`, the default value for which is 10,000. A new attribute, `SESSION_CONTEXT_SIZE`, which stores the number of sets of context information that are currently in the user session, is accessible as `SELECT SYS_CONTEXT``('userenv', 'session_context_size') from dual;`

- The client session-based application context, using only the `CLIENTCONTEXT` namespace, updatable by any OCI client or by the existing `DBMS_SESSION API` for application context. No privilege or package security check is done.

  The `CLIENTCONTEXT` namespace enables a single application transaction to both change the user context information and use the same user session handle to service the new user request.

  - An OCI client uses the `OCIAppCtx` API to set variable length data for the namespace, on the `OCISessionHandle`. The OCI network single round-trip transport sends all the information to the server in one round trip. On the server side, the application context information can be queried using the `SYS_CONTEXT` SQL function on the namespace.

  - A JDBC client uses the `oracle.jdbc.internal.OracleConnection` API to achieve the same purposes.

  See Also: Managing Scalable Platforms in Oracle Call Interface Programmer's Guide for details regarding the OCIAppCtx API Using Client Session-Based Application Context.

  Any user can set, clear, or collect the information in the `CLIENTCONTEXT` namespace, because it is not protected by package-based security.

- Nonsession-based (global) application contexts, for which data is stored in the shared area (SGA).

  See Also: How to Use Global Application Context

Table 15-1 summarizes the different types of application contexts.

Table 15-1 Types of Application Contexts

Application Context Type | Stored in UGA | Stored in SGA | Supports Connected User Database Links | Supports Centralized Storage of Users' Application Context | Supports Sessionless Multitier Applications
--- | --- | --- | --- | --- | ---
Application Context | X | -- | -- | -- | --
Application Context Initialized Externally | X | -- | X | -- | --
Application Context Initialized Globally | X | -- | -- | X | --
CLIENTCONTEXT | X | -- | X | -- | X
Global Application Context | -- | X | -- | -- | X

See Also: "Introduction to Application Context" for conceptual information about session-based application context "Introduction to Global Application Context" for conceptual information about nonsession-based application context "Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity" for a discussion about using the `CLIENT_IDENTIFIER` attribute of the predefined `USERENV` application context "Fine-Grained Auditing" for information about using application context with fine-grained auditing

The following sections explain secure and client session-based application context:

- Using Secure Session-Based Application Context

- Using Client Session-Based Application Context

## 15.2 Using Secure Session-Based Application Context

To use secure session-based application context, you must perform the following tasks:

- Task 1: Create a PL/SQL Package that Sets the Secure Context for Your Application

- Task 2: Create a Unique Secure Context and Associate It with the PL/SQL Package

- Task 3: Set the Secure Context Before the User Retrieves Data

- Task 4: Use the Secure Context in a VPD Policy Function

### 15.2.1 Task 1: Create a PL/SQL Package that Sets the Secure Context for Your Application

Begin by creating a PL/SQL package with functions that set the secure context for your application. This section presents the syntax and behavior of the `SYS_CONTEXT` SQL function, followed by an example for creating the PL/SQL package.

Note: A login trigger can be used because the user context (information such as `EMPNO`, `GROUP`, `MANAGER`) should be set before the user accesses any data.

#### 15.2.1.1 SYS_CONTEXT Syntax

The syntax for this function is:

    SYS_CONTEXT ('namespace', 'attribute', [length])

This function returns the value of `attribute` as defined in the package currently associated with the context namespace. It is evaluated once for each statement execution, and is treated like a constant during type checking for optimization. You can use the predefined namespace `USERENV` to access primitive contexts such as userid and Globalization Support parameters.

See Also: "Providing Access to Predefined Attributes Through the USERENV Namespace" for information about the `USERENV` application context namespace and a complete list of its predefined attributes Oracle Database SQL Reference for details about `USERENV` predefined attributes

#### 15.2.1.2 SYS_CONTEXT Example

The following example creates the package `App_security_context`.

    CREATE OR REPLACE PACKAGE App_security_context IS
       PROCEDURE Set_empno;
    END;

    CREATE OR REPLACE PACKAGE BODY App_security_context IS
       PROCEDURE Set_empno
       IS
       Emp_id NUMBER;
       BEGIN
        SELECT Empno INTO Emp_id FROM Emp
           WHERE Ename = SYS_CONTEXT('USERENV',
                                     'SESSION_USER');
        DBMS_SESSION.SET_CONTEXT('app_context', 'empno', Emp_id);
       END;
    END;

See Also: PL/SQL Packages and Types Reference for information about the `DBMS_SESSION.SET_CONTEXT` procedure.

#### 15.2.1.3 Using Dynamic SQL with SYS_CONTEXT

Note: This feature is applicable when `COMPATIBLE` is set to either 8.0 or 8.1.

During a session in which you expect a change in policy between executions of a given query, the query must use dynamic SQL. You must use dynamic SQL because static SQL and dynamic SQL parse statements differently.

- Static SQL statements are parsed at compile time. They are not reparsed at execution for performance reasons.

- Dynamic SQL statements are parsed every time they are executed.

Consider a situation in which policy A is in force when you compile a SQL statement, and then you switch to policy B and run the statement. With static SQL, policy A remains in force. The statement is parsed at compile time and not reparsed upon execution. With dynamic SQL, the statement is parsed upon execution, and so the switch to policy B takes effect.

For example, consider the following policy:

    EMPLOYEE_NAME = SYS_CONTEXT ('USERENV', 'SESSION_USER')

The policy `EMPLOYEE_NAME` matches the database user name. It is represented in the form of a SQL predicate: the predicate is basically a policy. If the predicate changes, then the statement must be reparsed in order to produce the correct result.

See Also: "Automatic Reparse"

#### 15.2.1.4 Using SYS_CONTEXT in a Parallel Query

If `SYS_CONTEXT` is used inside a SQL function that is embedded in a parallel query, then the function picks up the application context.

Consider a user-defined function within a SQL statement, which sets the user ID to 5:

    CREATE FUNCTION proc1 AS RETURN NUMBER;
    BEGIN
         IF SYS_CONTEXT ('hr', 'id') = 5
         THEN RETURN 1; ELSE RETURN 2;
         END
    END;

Now consider the statement:

    SELECT * FROM EMP WHERE proc1( ) = 1;

When this statement is run as a parallel query, the user session, which contains the application context information, is propagated to the parallel execution servers (query slave processes).

#### 15.2.1.5 Using SYS_CONTEXT with Database Links

Session-based local application context can be accessed by SQL statements within a user session by using the `SYS_CONTEXT` SQL function. When these SQL statements involve database links, then the `SYS_CONTEXT` SQL function is executed at the database link's initiating site and captures the context information there (at the initiating site).

If remote PL/SQL procedure calls are executed over a database link, then any `SYS_CONTEXT` function inside such a procedure is executed at the database link's destination site. In this case, only externally initialized application contexts are available at the database link destination site. For security reasons, only the externally initialized application context information is propagated to the destination site from the initiating database link site.

### 15.2.2 Task 2: Create a Unique Secure Context and Associate It with the PL/SQL Package

To perform this task, use the `CREATE` `CONTEXT` statement. Each context must have a unique attribute and belong to a namespace. That is, context names must be unique within the database, not just within a schema. Contexts are always owned by the `SYS` schema.

For example:

    CREATE CONTEXT order_entry USING App_security_context;

Here, `order_entry` is the context namespace and `App_security_context` is the trusted package that can set attributes in the context namespace.

After you have created the context, you can set or reset the context attributes by using the `DBMS_SESSION.SET_CONTEXT` package. The values of the attributes you set remain either until you reset them or until the user ends the session.

You can only set the context attributes inside the trusted procedure you named in the `CREATE` `CONTEXT` statement. This prevents a malicious user from changing context attributes without proper attribute validation.

Alternatively, you can use the Oracle Policy Manager graphical user interface (GUI) to create a context and associate it with a PL/SQL package. Oracle Policy Manager, accessed from Oracle Enterprise Manager, enables you to apply policies to database objects and create application contexts. It also can be used to create and manage Oracle Label Security policies.

### 15.2.3 Task 3: Set the Secure Context Before the User Retrieves Data

Always use an event trigger on login to pull session information into the context. This sets the security-limiting attributes of the user for the database to evaluate, and thus enables it to make the appropriate security decisions.

Other considerations come into play if you have a changing set of books, or if positions change constantly. In these cases, the new attribute values may not be picked up right away, and you must force a cursor reparse to pick them up.

### 15.2.4 Task 4: Use the Secure Context in a VPD Policy Function

Now that you have set up the context and the PL/SQL package, your VPD policy functions can use the application context to make policy decisions based on different context values.

## 15.3 Examples: Secure Application Context Within a Fine-Grained Access Control Function

This section provides the following examples that use secure session-based application context within a fine-grained access control function.

- Example 1: Implementing the Policy

- Example 2: Controlling User Access with an Application

- Example 3: Event Triggers, Secure Application Context, Fine-Grained Access Control, and Encapsulation of Privileges

### 15.3.1 Example 1: Implementing the Policy

This example uses secure application context to implement a policy, in which customers can see only their own orders, and builds the application using the following steps:

- Step 1: Create a PL/SQL Package To Set the Secure Context for the Application

- Step 2: Create a Secure Application Context

- Step 3: Access the Secure Application Context Inside the Package

- Step 4: Create the New Security Policy

The procedure in this example assumes a one-to-one relationship between users and customers. It finds the user customer number (`Cust_num`) and caches the customer number in the application context. You can later refer to the `cust_num` attribute of the order entry context (`oe_ctx`) inside the security policy function.

Note that you could use a login trigger to set the initial context.

#### 15.3.1.1 Step 1: Create a PL/SQL Package To Set the Secure Context for the Application

Create the package as follows:

Note: You may need to set up the following data structures for the following examples to work:
    CREATE TABLE apps.customers (cust_no NUMBER(4), cust_name VARCHAR2(20));
    CREATE TABLE scott.orders_tab (order_no NUMBER(4));

    CREATE OR REPLACE PACKAGE apps.oe_ctx AS
       PROCEDURE set_cust_num;
    END;

    CREATE OR REPLACE PACKAGE BODY apps.oe_ctx AS
       PROCEDURE set_cust_num IS
         custnum NUMBER;
       BEGIN
          SELECT cust_no INTO custnum FROM customers WHERE cust_name =
             SYS_CONTEXT('USERENV', 'SESSION_USER');
         /* SET cust_num attribute in 'order_entry' context */
             DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
             DBMS_SESSION.SET_CONTEXT('order_entry', 'cust_num', custnum);
       END set_cust_num;
    END;

Note: This example does not treat error handling. You can access predefined attributes, such as session user, by using `SYS_CONTEXT('USERENV',` `session_primitive``)`. For more information, see Table 14-1, "Key to Predefined Attributes in USERENV Namespace" and Oracle Database SQL Reference.

#### 15.3.1.2 Step 2: Create a Secure Application Context

Create an application context by entering:

    CREATE CONTEXT Order_entry USING apps.oe_ctx;

Alternatively, you can use Oracle Policy Manager to create an application context.

#### 15.3.1.3 Step 3: Access the Secure Application Context Inside the Package

Access the secure application context inside the package that implements the security policy on the database object.

Note: You may need to set up the following data structures for certain examples to work:
    CREATE OR REPLACE PACKAGE Oe_security AS
    FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2)
    RETURN VARCHAR2;
    END;

The package body appends a dynamic predicate to `SELECT` statements on the `ORDERS_TAB` table. This predicate limits the orders returned to those associated with the customer number of the user by accessing the `cust_num` context attribute, instead of using a subquery to the customers table.

    CREATE OR REPLACE PACKAGE BODY Oe_security AS

    /* limits select statements based on customer number: */
    FUNCTION Custnum_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2
    IS
        D_predicate VARCHAR2 (2000);
        BEGIN
         D_predicate := 'cust_no = SYS_CONTEXT(''order_entry'', ''cust_num'')';
         RETURN D_predicate;
        END Custnum_sec;
    END Oe_security;

#### 15.3.1.4 Step 4: Create the New Security Policy

Note: You may need to set up the following data structures for certain examples to work:
    CONNECT sys/xIcf1T9u AS sysdba;
    CREATE USER secusr IDENTIFIED BY secusr;

Create the policy as follows:

    BEGIN
    DBMS_RLS.ADD_POLICY ('scott', 'orders_tab', 'oe_policy', 'secusr',
                         'oe_security.custnum_sec', 'select');
    END;

This statement adds a policy named `OE_POLICY` to the `ORDERS_TAB` table for viewing in the `SCOTT` schema. The `SECUSR`.`OE_SECURITY.CUSTNUM_SEC` function implements the policy, is stored in the `SECUSR` schema, and applies to `SELECT` statements only.

Now, any `SELECT` statement by a customer on the `ORDERS_TAB` table automatically returns only the orders of that particular customer. In other words, the dynamic predicate modifies the statement from:

    SELECT * FROM Orders_tab;

to the following:

    SELECT * FROM Orders_tab
       WHERE Custno = SYS_CONTEXT('order_entry','cust_num');

Note the following with regard to this example:

- In reality, you might have several predicates based on a user's position. For example, a sales representative would be able to see records only for his customers, and an order entry clerk would be able to see any customer order. You could expand the `custnum_sec` function to return different predicates based on the user position context value.

- The use of application context in a fine-grained access control package effectively gives you a bind variable in a parsed statement. For example:

      SELECT * FROM Orders_tab
         WHERE Custno = SYS_CONTEXT('order_entry', 'cust_num')

  This is fully parsed and optimized, but the evaluation of the `CUST_NUM` attribute value of the user for the `ORDER_ENTRY` context takes place at execution. This means that you get the benefit of an optimized statement that executes differently for each user who executes the statement.

  Note: You can improve the performance of the function in this example by indexing `CUST_NO`.

- You can set context attributes based on data from a database table or tables, or from a directory server using LDAP (Lightweight Directory Access Protocol).

  See Also: Using Triggers in Oracle Database Application Developer's Guide - Fundamentals "Optimizing Performance by Enabling Static and Context Sensitive Policies" "Adding Policies for Column-Level VPD"

Compare and contrast this example, which uses an application context within the dynamically generated predicate, with "How Fine-Grained Access Control Works", which uses a subquery in the predicate.

### 15.3.2 Example 2: Controlling User Access with an Application

This example uses secure application context to control user access with a Human Resources application. Each task that you need to perform is described more fully in the sections that follow:

- Step 1: Create a PL/SQL Package to Set the Secure Context

- Step 2: Create the Secure Context and Associate It with the Package

- Step 3: Create the Initialization Script for the Application

In this example, assume that the application context for the Human Resources application is assigned to the `HR_CTX` namespace.

#### 15.3.2.1 Step 1: Create a PL/SQL Package to Set the Secure Context

Note: You may need to set up the following data structures for certain examples to work:
    DROP USER apps CASCADE;
    CREATE USER apps IDENTIFIED BY welcome1;

    CREATE OR REPLACE PACKAGE apps.hr_sec_ctx IS
       PROCEDURE set_resp_id (respid NUMBER);
       PROCEDURE set_org_id (orgid NUMBER);
      /* PROCEDURE validate_respid (respid NUMBER); */
      /* PROCEDURE validate_org_id (orgid NUMBER); */
    END hr_sec_ctx;

Create a PL/SQL package with a number of functions that set the secure context for the application. `APPS` is the schema that owns this package.

    CREATE OR REPLACE PACKAGE BODY apps.hr_sec_ctx IS
    /* function to set responsibility id */
    PROCEDURE set_resp_id (respid NUMBER) IS
    BEGIN

    /* validate respid based on primitive and other context */
    /*    validate_respid (respid); */
    /* set resp_id attribute under namespace 'hr_ctx'*/

        DBMS_SESSION.SET_CONTEXT('hr_ctx', 'resp_id', respid);
    END set_resp_id;

    /* function to set organization id */
    PROCEDURE set_org_id (orgid NUMBER) IS
    BEGIN

    /* validate organization ID */
    /*    validate_org_id(orgid); /*
    /* set org_id attribute under namespace 'hr_ctx' */

        DBMS_SESSION.SET_CONTEXT('hr_ctx', 'org_id', orgid);
    END set_org_id;

    /* more functions to set other attributes for the HR application */
    END hr_sec_ctx;

#### 15.3.2.2 Step 2: Create the Secure Context and Associate It with the Package

For example:

    CREATE CONTEXT Hr_ctx USING apps.hr_sec_ctx;

#### 15.3.2.3 Step 3: Create the Initialization Script for the Application

Suppose that the execute privilege on the `HR_SEC_CTX` package has been granted to the schema running the application. Part of the script will make calls to set various attributes of the `HR_CTX` context. Here, we do not show how the context is determined. Normally, it is based on the primitive context or other derived context.

    APPS.HR_SEC_CTX.SET_RESP_ID(1);
    APPS.HR_SEC_CTX.SET_ORG_ID(101);

The `SYS_CONTEXT` function can be used for data access control based on this application context. For example, the base table `HR_ORGANIZATION_UNIT` can be secured by a view that restricts access to rows based on the attribute `ORG_ID`:

    CREATE VIEW Hr_organization_secv AS
       SELECT * FROM hr_organization_unit
          WHERE Organization_id = SYS_CONTEXT('hr_ctx','org_id');

Note: You may need to set up the following data structures for certain examples to work:
    CREATE TABLE hr_organization_unit (organization_id NUMBER);

### 15.3.3 Example 3: Event Triggers, Secure Application Context, Fine-Grained Access Control, and Encapsulation of Privileges

This example illustrates use of the following security features in Oracle Database:

- Event triggers

- Application context (session-based)

- Fine-grained access control

- Encapsulation of privileges in stored procedures

In this example, we associate a security policy with the table called `DIRECTORY`, which has the following columns:

Column | Description
--- | ---
`EMPNO` | Identification number for each employee
`MGRID` | Employee identification number for the manager of each employee
`RANK` | Position of the employee in the corporate hierarchy

Note: You may need to set up the following data structures for certain examples to work:
    CREATE TABLE Payroll(
       Srate  NUMBER,
       Orate  NUMBER,
       Acctno NUMBER,
       Empno  NUMBER,
       Name   VARCHAR2(20));
    CREATE TABLE Directory_u(
       Empno NUMBER,
       Mgrno NUMBER,
       Rank  NUMBER);
    CREATE SEQUENCE Empno_seq;
    CREATE SEQUENCE Rank_seq;

The security policy associated with this table has two elements:

- All users can find the `MGRID` for a specific `EMPNO`. To implement this, we create a definer's right package in the human resources schema (`HR`) to perform `SELECT` on the table.

- Managers can update only those positions in the corporate hierarchy that are their direct subordinates. To do this, they must use only the designated application. You can implement this as follows:

  - Define fine-grained access control policies on the table based on `EMPNO` and application context.

  - Set `EMPNO` by using a login trigger.

  - Set the application context by using the designated package for processing the updates (event triggers and application context).

    Note: In this example, we grant `UPDATE` privileges on the table to `PUBLIC`, because fine-grained access control prevents an unauthorized user from wrongly modifying a given row.

The following code implements this example as described:

    CONNECT system/yJdg2U1v AS sysdba
    GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE,CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE ANY TRIGGER TO HR IDENTIFIED BY HR;
    CONNECT hr/hr;
    CREATE TABLE Directory (Empno   NUMBER(4) NOT NULL,
                            Mgrno   NUMBER(4) NOT NULL,
                            Rank    NUMBER(7,2) NOT NULL);

    CREATE TABLE Payroll (Empno  NUMBER(4) NOT NULL,
                          Name   VARCHAR(30) NOT NULL );

    /* seed the tables with a couple of managers: */
    INSERT INTO Directory VALUES (1, 1, 1.0);
    INSERT INTO Payroll VALUES (1, 'KING');
    INSERT INTO Directory VALUES (2, 1, 5);
    INSERT INTO Payroll VALUES (2, 'CLARK');

    /* Create the sequence number for EMPNO: */
    CREATE SEQUENCE Empno_seq START WITH 5;

    /* Create the sequence number for RANK:  */
    CREATE SEQUENCE Rank_seq START WITH 100;

    CREATE OR REPLACE CONTEXT Hr_app USING Hr.Hr0_pck;
    CREATE OR REPLACE CONTEXT Hr_sec USING Hr.Hr1_pck;

    CREATE OR REPLACE PACKAGE Hr0_pck IS
    PROCEDURE adjustrankby1(Empno NUMBER);
    END;

    CREATE OR REPLACE PACKAGE BODY Hr0_pck IS
    /* raise the rank of the empno by 1:  */
    PROCEDURE Adjustrankby1(Empno NUMBER)
    IS
       Stmt   VARCHAR2(100);
       BEGIN

       /*Set context to indicate application state */
       DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',1);
       /* Now we can issue DML statement:  */
       Stmt := 'UPDATE Directory d SET Rank = Rank + 1 WHERE d.Empno = '
       || Empno;
       EXECUTE IMMEDIATE STMT;

    /* Re-set application state: */
       DBMS_SESSION.SET_CONTEXT('hr_app','adjstate',0);
       END;
    END;

    CREATE OR REPLACE PACKAGE hr1_pck IS PROCEDURE setid;
    END;
    /
    /* Based on userid, find EMPNO, and set it in application context */

    CREATE or REPLACE PACKAGE BODY Hr1_pck IS
    PROCEDURE setid
      IS
      id NUMBER;
      BEGIN
        SELECT Empno INTO id FROM Payroll WHERE Name =
          SYS_CONTEXT('userenv','session_user') ;
        DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id);
        DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id);
      EXCEPTION
        /* For purposes of demonstration insert into payroll table
        /  so that user can continue on and run example. */
        WHEN NO_DATA_FOUND THEN
          INSERT INTO Payroll (Empno, Name)
             VALUES (Empno_seq.NEXTVAL, SYS_CONTEXT('userenv','session_user'));
          INSERT INTO Directory (Empno, Mgrno, Rank)
             VALUES (Empno_seq.CURRVAL, 2, Rank_seq.NEXTVAL);
          SELECT Empno INTO id FROM Payroll WHERE Name =
            sys_context('userenv','session_user') ;
          DBMS_SESSION.SET_CONTEXT('hr_sec','empno',id);
          DBMS_SESSION.SET_CONTEXT('hr_sec','appid',id);
        WHEN OTHERS THEN
          NULL;
        /* If this is to be fired by using a "logon" trigger,
        /  you need to handle exceptions if you want the user to continue
        /  logging into the database. */
      END;
    END;

    GRANT EXECUTE ON Hr1_pck TO public;

    CONNECT system/yJdg2U1v AS sysdba

    CREATE OR REPLACE TRIGGER Databasetrigger

    AFTER LOGON
    ON DATABASE
    BEGIN
       hr.Hr1_pck.Setid;
    END;

    /* Creates the package for finding the MGRID for a particular EMPNO
    using definer's right (encapsulated privileges). Note that users are
    granted EXECUTE privileges only on this package, and not on the table
    (DIRECTORY) it is querying. */

    CONNECT hr/hr

    CREATE OR REPLACE PACKAGE hr2_pck IS
       FUNCTION Findmgr(Empno NUMBER) RETURN NUMBER;
    END;

    CREATE OR REPLACE PACKAGE BODY hr2_pck IS
       /* insert a new employee record: */
       FUNCTION findmgr(empno number) RETURN NUMBER IS
       Mgrid NUMBER;
       BEGIN
          SELECT mgrno INTO mgrid FROM directory WHERE mgrid = empno;
       RETURN mgrid;
       END;
    END;

    CREATE OR REPLACE FUNCTION secure_updates(ns varchar2,na varchar2)
      RETURN VARCHAR2 IS
         Results VARCHAR2(100);
        BEGIN
           /* Only allow updates when designated application has set the session
           state to indicate we are inside it. */
           IF (sys_context('hr_app','adjstate') = 1)
              THEN results := 'mgrno = SYS_CONTEXT("hr_sec","empno")';
           ELSE results := '1=2';
          END IF;
          RETURN Results;
       END;

    /* Attaches fine-grained access policy to all update operations on
    hr.directory */

    CONNECT system/yJdg2U1v AS sysdba;
    BEGIN
       DBMS_RLS.ADD_POLICY('hr','directory','secure_update','hr',
                           'secure_updates','update',TRUE,TRUE);
    END;

## 15.4 Initializing Secure Application Context Externally

This feature lets you specify a special type of namespace that accepts initialization of attribute values from external resources and stores them in the local user session. Allowing secure application context to be initialized externally enhances performance and enables the automatic propagation of attributes from one session to another. Connected user database links are supported only by application contexts initialized from OCI-based external sources.

This section contains these topics:

- Obtaining Default Values from Users

- Obtaining Values from Other External Resources

### 15.4.1 Obtaining Default Values from Users

Sometimes it is desirable to obtain default values from users. Initially, these default values may serve as hints or preferences, and then after validation become trusted contexts. Similarly, it may be more convenient for clients to initialize some default values, and then rely on a login event trigger or applications to validate the values.

For job queues, the job submission routine records the context being set at the time the job is submitted, and restores it when executing the batched job. To maintain the integrity of the context, job queues cannot bypass the designated PL/SQL package to set the context. Rather, externally initialized application context accepts initialization of context values from the job queue process.

Automatic propagation of context to a remote session may create security problems. Developers or administrators can effectively handle this type of context that takes default values from resources other than the designated PL/SQL procedure by using login triggers to reset the context when users log in.

### 15.4.2 Obtaining Values from Other External Resources

In addition to using the designated trusted package, externally initialized secure application contexts can also accept initialization of attributes and values through external resources. Examples include an OCI interface, a job queue process, or a database link.

Externally initialized secure application context provides:

- For remote sessions, automatic propagation of context values that are in the externally initialized secure context namespace

- For job queues, restoration of context values that are in the externally initialized secure context namespace

- For OCI interfaces, a mechanism to initialize context values that are in the externally initialized secure context namespace

Although this type of namespace can be initialized by any client program using OCI, there are login event triggers that can verify the values. It is up to the application to interpret and trust the values of the attributes.

Middle-tier servers can actually initialize secure context values on behalf of database users. Context attributes are propagated for the remote session at initialization time, and the remote database accepts the values if the namespace is externally initialized.

See Also: Oracle Database JDBC Developer's Guide and Reference Oracle Call Interface Programmer's Guide

## 15.5 Initializing Secure Application Context Globally

This feature uses a centralized location to store the secure application context of the user, enabling applications to set up a user context during initialization based upon user identity. In particular, it supports Oracle Label Security labels and privileges. This feature makes it much easier for the administrator to manage contexts for large numbers of users and databases.

For example, many organizations want to manage user information centrally, in an LDAP-based directory. Enterprise User Security, a feature of Oracle Advanced Security, supports centralized user and authorization management in Oracle Internet Directory. However, there may be additional attributes an application wishes to retrieve from LDAP to use for VPD enforcement, such as the user title, organization, or physical location.

This section contains these topics:

- Using Secure Application Context with LDAP

- How Globally Initialized Secure Application Context Works

- Example: Initializing Secure Application Context Globally

### 15.5.1 Using Secure Application Context with LDAP

Secure session-based application context initialized globally uses the Lightweight Directory Access Protocol (LDAP), a standard, extensible, and efficient directory access protocol. The LDAP directory stores a list of users to which this application is assigned. An Oracle database server uses the directory service, typically Oracle Internet Directory, for authentication and authorization of enterprise users.

Note: Enterprise User Security requires Oracle Advanced Security. Third-party directories such as Microsoft Active Directory and Sun Microsystems iPlanet can also be used as the directory service.

The `orclDBApplicationContext` LDAP object (a subclass of `groupOfUniqueNames`) stores the application context values in the directory. The location of the application context object is described in Figure 15-1, which is based on the Human Resources example.

An internal C function is required to retrieve the `orclDBApplicationContext` value, which returns a list of application context values to the RDBMS.

Note: In this example, HR is the namespace, Title and Project are the attributes, and Manager and Promotion are the values.

Figure 15-1 Location of Application Context in LDAP Directory Information Tree

Description of the illustration adfns001.gif

### 15.5.2 How Globally Initialized Secure Application Context Works

The administrator configures Enterprise User Security, a feature of Oracle Advanced Security. Then, the administrator sets up the secure application context values for the user in the database and the directory.

When a global user (enterprise user) connects to the database, the Oracle Advanced Security Enterprise User Security feature performs authentication to verify the identity of the user connecting to the database. After authentication, the global user roles and application context are retrieved from the directory. When the user logs on to the database, the global roles and initial application context are already set up.

See Also: Oracle Database Advanced Security Administrator's Guide for a detailed discussion of Enterprise User Security and how to configure this feature

### 15.5.3 Example: Initializing Secure Application Context Globally

The initial application context for a user, such as department name and title, can be set up and stored in the LDAP directory. The values are retrieved during user login so that the context is set properly. In addition, any information related to the user is retrieved and stored in the `SYS_USER_DEFAULTS` application context namespace. The following example shows how this is done.

1. Create a secure application context in the database.

       CREATE CONTEXT HR USING hrapps.hr_manage_pkg INITIALIZED GLOBALLY;

2. Create and add new entries in the LDAP directory.

   An example of the entries added to the LDAP directory follows. These entries create an attribute named `Title` with attribute value `Manager` for the application (namespace) `HR`, and assign user names `user1` and `user2`.

       dn: cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=OracleC
       ontext,ou=Americas,o=oracle,c=US
       changetype: add
       cn: OracleDBAppContext
       objectclass: top
       objectclass: orclContainer

       dn: cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=O
       racleContext,ou=Americas,o=oracle,c=US
       changetype: add
       cn: HR
       objectclass: top
       objectclass: orclContainer

       dn: cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Prod
       ucts,cn=OracleContext,ou=Americas,o=oracle,c=US
       changetype: add
       cn: Title
       objectclass: top
       objectclass: orclContainer

       dn: cn=Manager,cn=Title,cn=HR,cn=OracleDBAppContext,cn=myDomain,cn=OracleDBSecurity,cn=Products,cn=OracleContext,ou=Americas,o=oracle,c=US
       cn: Manager
       objectclass: top
       objectclass: groupofuniquenames
       objectclass: orclDBApplicationContext
       uniquemember: CN=user1,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US
       uniquemember: CN=user2,OU=Americas,O=Oracle,L=Redwoodshores,ST=CA,C=US

3. If an LDAP `inetOrgPerson` object entry exists for the user, then the connection will also retrieve all the attributes from `inetOrgPerson` and assign them to the namespace `SYS_LDAP_USER_DEFAULT`. The following is an example of an `inetOrgPerson` entry:

       dn: cn=user1,ou=Americas,O=oracle,L=redwoodshores,ST=CA,C=US
       changetype: add
       objectClass: top
       objectClass: person
       objectClass: organizationalPerson
       objectClass: inetOrgPerson
       cn: user1
       sn: One
       givenName: User
       initials: UO
       title: manager, product development
       uid: uone
       mail: uone@us.oracle.com
       telephoneNumber: +1 650 123 4567
       employeeNumber: 00001
       employeeType: full time

4. Connect to the database.

   When `user1` connects to a database that belongs to the `myDomain` domain, `user1` will have his `Title` set to `Manager`. Any information related to `user1` will be retrieved from the LDAP directory. The value can be obtained using the following syntax:

       SYS_CONTEXT('namespace','attribute name')

   For example:

       DECLARE
       tmpstr1 VARCHAR2(30);
       tmpstr2 VARCHAR2(30);
       BEGIN
       tmpstr1 = SYS_CONTEXT('HR','TITLE);
       tmpstr2 = SYS_CONTEXT('SYS_LDAP_USER_DEFAULT','telephoneNumber');
       DBMS_OUTPUT.PUT_LINE('Title is ' || tmpstr1);
       DBMS_OUTPUT.PUT_LINE('Telephone Number is ' || tmpstr2);
       END;

   The output of this example is:

       Title is Manager
       Telephone Number is +1 650 123 4567

## 15.6 Using Client Session-Based Application Context

The `OCIAppCtx` API enables you to use client session-based application context. You can set or clear individual values for attributes in the `CLIENTCONTEXT` namespace, or clear all their values, by following the examples in the subsections that follow:

- Setting a Value in CLIENTCONTEXT

- Clearing a Particular Setting in CLIENTCONTEXT

- Clearing all Settings in CLIENTCONTEXT

### 15.6.1 Setting a Value in CLIENTCONTEXT

For OCI, use a command of the following form:

-     err = OCIAppCtxSet((void *) session_handle,(dvoid *)"CLIENTCONTEXT",(ub4) 13,                   (dvoid *)attribute_name, length_of_attribute_name
                         (dvoid *)attribute_value, length_of_attribute_value, errhp,
                         OCI_DEFAULT);

  where `session_handle` represents the `OCISessionHandle`, `attribute_name` could be, for example, "responsibility", with a length of 14, and `attribute_value` could be, for example, "manager", with a length of 7.

For JDBC, use a command of the following form:

-     public void setApplicationContext(String CLIENTCONTEXT, string attribute, string value) throws SQLException;

  where `attribute` represents the attribute whose value needs to be set, and `value` is the value to be assigned to that attribute.

### 15.6.2 Clearing a Particular Setting in CLIENTCONTEXT

For OCI, set the value to NULL or to an empty string by using one of the following command forms:

-     (void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13,
                         (dvoid *)attribute_name, length_of_attribute_name,
                         (dvoid *)0, 0,errhp,
                         OCI_DEFAULT);

  or

-     (void) OCIAppCtxSet((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13
                         (dvoid *)attribute_name, length_of_attribute_name,
                         (dvoid *)"", 0,errhp,
                         OCI_DEFAULT);

For JDBC, use the following command:

-     public void setApplicationContext(String CLIENTCONTEXT, string attribute, string value) throws SQLException;

  where `attribute` represents the attribute whose value needs to be cleared and `value` is either 0 or the null string `""`.

### 15.6.3 Clearing all Settings in CLIENTCONTEXT

For OCI, use a command of the following form:

-     err = OCIAppCtxClearAll((void *) session_handle, (dvoid *)"CLIENTCONTEXT", 13,
                              errhp,                        OCI_DEFAULT);

For JDBC, use a command of the following form:

-     public void clearAllApplicationContext(String CLIENTCONTEXT) throws SQLException;

## 15.7 How to Use Global Application Context

Global application context stores context information in the System Global Area (SGA) so that it can be used for applications that use a sessionless model, such as middle-tier applications in a three-tiered architecture. These applications cannot use session-based application context because users authenticate to the application and then it typically connects to the database as a single identity. Global application context uses the `CLIENT_IDENTIFIER` `USERENV` namespace attribute, set using the `DBMS_SESSION` interface, to associate the database session with a particular user or group. The following sections explain how to use the `DBMS_SESSION` interface to set the `CLIENT_IDENTIFIER` and then provide examples:

- Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions

- Examples: Global Application Context

  See Also: "Introduction to Global Application Context" for conceptual information about this feature

### 15.7.1 Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions

The `DBMS_SESSION` interface for managing application context has a client identifier for each application context. In this way, application context can be managed globally, yet each client sees only his or her assigned application context. The following interfaces in `DBMS_SESSION` enable the administrator to manage application context in client sessions:

- `SET_CONTEXT`

- `CLEAR_CONTEXT`

- `CLEAR_ALL_CONTEXT` (can also be used with session-based application context)

- `SET_IDENTIFIER`

- `CLEAR_IDENTIFIER`

The middle-tier application server can use `SET_CONTEXT` to set application context for a specific client ID. Then, when assigning a database connection to process the client request, the application server needs to issue a `SET_IDENTIFIER` to denote the ID of the application session. From then on, every time the client invokes `SYS_CONTEXT`, only the context that was associated with the set identifier is returned.

See Also: PL/SQL Packages and Types Reference for reference information and a complete description of the `DBMS_SESSION` package "Using CLIENT_IDENTIFIER Independent of Global Application Context" for information about setting this `USERENV` namespace attribute with the `DBMS_SESSION` interface

### 15.7.2 Examples: Global Application Context

This section provides two examples that use global application context.

- Example 1: Global Application Context Process

- Example 2: Global Application Context for Lightweight Users

#### 15.7.2.1 Example 1: Global Application Context Process

The following steps outline the global application context process:

1. Consider the application server, `AppSvr`, that has assigned the client identifier `12345` to client `SCOTT`. It then issues the following statement to indicate that, for this client identifier, there is an application context called `RESPONSIBILITY` with a value of `13` in the `HR` namespace.

       DBMS_SESSION.SET_CONTEXT( 'HR', 'RESPONSIBILITY' , '13', 'SCOTT', '12345' );

   Note that `HR` must be a global context namespace created as follows:

       CREATE CONTEXT hr USING hr.init ACCESSED GLOBALLY;

2. Then, the following command is issued to indicate the connecting client identity each time `SCOTT` uses `AppSvr` to connect to the database:

       DBMS_SESSION.SET_IDENTIFIER('12345');

3. When there is a `SYS_CONTEXT('HR','RESPONSIBILITY')` call within the database session, the database engine matches the client identifier `12345` to the global context, and returns the value `13`.

4. When exiting this database session, `AppSvr` clears the client identifier by issuing:

       DBMS_SESSION.CLEAR_IDENTIFIER( );

Note: After a client identifier in a session is cleared, it takes on a `NULL` value. This implies that subsequent `SYS_CONTEXT` calls only retrieve application contexts with `NULL` client identifiers, until the client identifier is set again using the `SET_IDENTIFIER` interface.

#### 15.7.2.2 Example 2: Global Application Context for Lightweight Users

The following steps outline the global application context process for a lightweight user application:

1. The administrator creates the global context namespace by using the following statement:

       CREATE CONTEXT hr USING hr.init ACCESSED GLOBALLY;

2. The `HR` application server (`AppSvr`) starts up and establishes multiple connections to the `HR` database as the `APPSMGR` user.

3. User `SCOTT` logs in to the `HR` application server.

4. `AppSvr` authenticates `SCOTT` to the application.

5. `AppSvr` assigns a temporary session ID (or simply uses the application user ID), `12345`, for this connection.

6. The session ID is returned to the browser used by `SCOTT` as part of a cookie or maintained by `AppSvr`.

   Note: If the application generates a session ID for use as a `CLIENT_IDENTIFIER`, then the session ID must be suitably random and protected over the network by encryption. If the session ID is not random, then a malicious user could guess the session ID and access the data of another user. If the session ID is not encrypted over the network, then a malicious user could retrieve the session ID and access the connection.

7. `AppSvr` initializes application context for this client by calling the `HR.INIT` package, which issues the following statements:

       DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', 'APPSMGR', 12345 );
       DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', 'APPSMGR', 12345 );

8. `AppSvr` assigns a database connection to this session and initializes the session by issuing the following statement:

       DBMS_SESSION.SET_IDENTIFIER( 12345 );

9. All `SYS_CONTEXT` calls within this database session will return application context values belonging only to the client session. For example, `SYS_CONTEXT('hr','id')` will return the value `SCOTT`.

10. When done with the session, `AppSvr` can issue the following statement to clean up the client identity:

        DBMS_SESSION.CLEAR_IDENTIFIER ( );

Note that even if another database user (`ADAMS`) had logged into the database, he cannot access the global context set by `AppSvr` because `AppSvr` has specified that only the application with logged in user `APPSMGR` can see it. If `AppSvr` has used the following, then any user session with client ID set to `12345` can see the global context.

    DBMS_SESSION.SET_CONTEXT( 'hr', 'id', 'scott', NULL , 12345 );
    DBMS_SESSION.SET_CONTEXT( 'hr', 'dept', 'sales', NULL , 12345 );

This approach enables different users to share the same context.

Note: Users must be aware of the security implication of different settings of the global context. `NULL` in the user name means that any user can access the global context. A `NULL` client ID in the global context means that only a session with an uninitialized client ID can access the global context.

Users can query the client identifier set in the session as follows:

    SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')

The DBA can see which sessions have the client identifier set by querying the `V$SESSION` view for the `CLIENT_IDENTIFIER` and `USERNAME`.

When users want to see the amount of global context area (in bytes) being used, they can use `SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY')`.

See Also: For more information about using the `CLIENT_IDENTIFIER` predefined attribute of the `USERENV` application context: "Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity" Oracle Database SQL Reference PL/SQL Packages and Types Reference Oracle Database JDBC Developer's Guide and Reference Oracle Call Interface Programmer's Guide

## 15.8 How Fine-Grained Access Control Works

Fine-grained access control is based on dynamically modified statements. Suppose you want to associate the `ORDERS_TAB` table with the following security policy: Customers can see only their own orders. The process is described in this section.

1. Create a function to add a predicate to a DML statement run by a user.

   Note: A predicate is the `WHERE` clause (a selection criterion clause) based on one of the operators (`=`, `!=`, `IS`, `IS NOT`, `>`, `>=`, `EXIST`, `BETWEEN`, `IN`, `NOT IN`, and so on). For a complete list of operators, refer to the Oracle Database SQL Reference.

   In this case, you might create a function that adds the following predicate:

       Cust_no = (SELECT Custno FROM Customers WHERE Custname =
                   SYS_CONTEXT ('userenv','session_user'))

2. A user enters the following statement:

       SELECT * FROM Orders_tab;

3. The Oracle Database server calls the function you created to implement the security policy.

4. The function dynamically modifies the statement entered by the user to read as follows:

       SELECT * FROM Orders_tab WHERE Custno = (
          SELECT Custno FROM Customers
              WHERE Custname = SYS_CONTEXT('userenv', 'session_user'))

5. The Oracle Database server runs the dynamically modified statement.

Upon execution, the function employs the user name returned by `SYS_CONTEXT ('userenv','session_user')` to look up the corresponding customer and to limit the data returned from the `ORDERS_TAB` table to that associated with that particular customer only.

See Also: For more information on using fine-grained access control: "Introduction to Fine-Grained Access Control" "Introduction to Global Application Context" PL/SQL Packages and Types Reference

## 15.9 How to Establish Policy Groups

A policy group is a set of security policies that belong to an application. You can designate an application context (known as a driving context) to indicate the policy group in effect. Then, when the table, view, or synonym column is accessed, the server looks up the driving context (which is also known as policy context) to determine the policy group in effect. It enforces all the associated policies which belong to that policy group.

This section contains the following topics:

- The Default Policy Group: SYS_DEFAULT

- New Policy Groups

- How to Implement Policy Groups

- Validating the Application Used to Connect to the Database

### 15.9.1 The Default Policy Group: SYS_DEFAULT

In the Oracle Policy Manager tree structure, the Fine-Grained Access Control Policies folder contains the Policy Groups folder. The Policy Groups folder contains an icon for each policy group, as well as an icon for the `SYS_DEFAULT` policy group.

By default, all policies belong to the `SYS_DEFAULT` policy group. Policies defined in this group for a particular table, view, or synonym will always be executed along with the policy group specified by the driving context. The `SYS_DEFAULT` policy group may or may not contain policies. If you attempt to drop the `SYS_DEFAULT` policy group, then an error will be raised.

If, to the `SYS_DEFAULT` policy group, you add policies associated with two or more objects, then each such object will have a separate `SYS_DEFAULT` policy group associated with it. For example, the `EMP` table in the `SCOTT` schema has one `SYS_DEFAULT` policy group, and the `DEPT` table in the `SCOTT` schema has a different `SYS_DEFAULT` policy group associated with it. These are displayed in the tree structure as follows:

    SYS_DEFAULT
      - policy1 (SCOTT/EMP)
      - policy3 (SCOTT/EMP)
    SYS_DEFAULT
      - policy2 (SCOTT/DEPT)

Note: Policy groups with identical names are supported. When you select a particular policy group, its associated schema and object name are displayed in the property sheet on the right-hand side of the screen.

### 15.9.2 New Policy Groups

When adding the policy to a table, view, or synonym, you can use the `DBMS_RLS.ADD_GROUPED_POLICY` interface to specify the group to which the policy belongs. To specify which policies will be effective, you add a driving context using the `DBMS_RLS.ADD_POLICY_CONTEXT` interface. If the driving context returns an unknown policy group, then an error is returned.

If the driving context is not defined, then all policies are executed. Likewise, if the driving context is `NULL`, then policies from all policy groups are enforced. In this way, an application accessing the data cannot bypass the security setup module (which sets up application context) to avoid any applicable policies.

You can apply multiple driving contexts to the same table, view, or synonym, and each of them will be processed individually. In this way, you can configure multiple active sets of policies to be enforced.

Consider, for example, a hosting company that hosts Benefits and Financial applications, which share some database objects. Both applications are striped for hosting using a `SUBSCRIBER` policy in the `SYS_DEFAULT` policy group. Data access is partitioned first by subscriber ID, then by whether the user is accessing the Benefits or Financial applications (determined by a driving context). Suppose that Company A, which uses the hosting services, wants to apply a custom policy which relates only to its own data access. You could add an additional driving context (such as `COMPANY A SPECIAL`) to ensure that the additional, special policy group is applied for data access for Company A only. You would not apply this under the `SUBSCRIBER` policy, because the policy relates only to Company A, and it is more efficient to segregate the basic hosting policy from other policies.

### 15.9.3 How to Implement Policy Groups

To create policy groups, the administrator must do two things:

- Set up a driving context to identify the effective policy group.

- Add policies to policy groups as required.

The following example shows how to perform these tasks.

Note: You need to set up the following data structures for the examples in this section to work:
    DROP USER finance CASCADE;
    CREATE USER finance IDENTIFIED BY welcome2;
    GRANT RESOURCE TO apps;
    DROP TABLE apps.benefit;
    CREATE TABLE apps.benefit (c NUMBER);

#### 15.9.3.1 Step 1: Set Up a Driving Context

Begin by creating a namespace for the driving context. For example:

    CREATE CONTEXT appsctx USING apps.apps_security_init;

Create the package that administers the driving context. For example:

    CREATE OR REPLACE PACKAGE apps.apps_security_init IS
    PROCEDURE setctx (policy_group VARCHAR2);
    END;

    CREATE OR REPLACE PACKAGE BODY apps.apps_security_init AS
    PROCEDURE setctx ( policy_group varchar2 ) IS
    BEGIN

    REM  Do some checking to determine the current application.
    REM  You can check the proxy if using the proxy authentication feature.
    REM  Then set the context to indicate the current application.
    .
    .
    .
    DBMS_SESSION.SET_CONTEXT('APPSCTX','ACTIVE_APPS', policy_group);
    END;
    END;

Define the driving context for the table `APPS.BENEFIT`.

    BEGIN
    DBMS_RLS.ADD_POLICY_CONTEXT('apps','benefit','APPSCTX','ACTIVE_APPS');
    END;

#### 15.9.3.2 Step 2: Add a Policy to the Default Policy Group.

Create a security function to return a predicate to divide the data by company.

    CREATE OR REPLACE FUNCTION by_company (sch varchar2, tab varchar2)
    RETURN VARCHAR2 AS
    BEGIN
      RETURN 'COMPANY = SYS_CONTEXT(''ID'',''MY_COMPANY'')';
    END;

Because policies in `SYS_DEFAULT` are always executed (except for `SYS`, or users with the `EXEMPT ACCESS POLICY` system privilege), this security policy (named `SECURITY_BY_COMPANY`), will always be enforced regardless of the application running. This achieves the universal security requirement on the table: namely, that each company should see its own data regardless of the application that is running. The function `APPS.APPS_SECURITY_INIT.BY_COMPANY` returns the predicate to make sure that users can only see data related to their own company:

    BEGIN
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','SYS_DEFAULT',
    'security_by_company',
    'apps','by_company');
    END;

#### 15.9.3.3 Step 3: Add a Policy to the HR Policy Group

First, create the `HR` group:

    CREATE OR REPLACE FUNCTION hr.security_policy
    RETURN VARCHAR2
    AS
    BEGIN
                     RETURN 'SYS_CONTEXT(''ID'',''TITLE'') = ''MANAGER'' ';
    END;

The following creates the policy group and adds a policy named `HR_SECURITY` to the `HR` policy group. The function `HR.SECURITY_POLICY` returns the predicate to enforce security on the `APPS.BENEFIT` table:

    BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','HR');
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','HR',
    'hr_security','hr','security_policy');
    END;

#### 15.9.3.4 Step 4: Add a Policy to the FINANCE Policy Group

Create the `FINANCE` policy:

    CREATE OR REPLACE FUNCTION finance.security_policy
    RETURN VARCHAR2
    AS
    BEGIN
                     RETURN ('SYS_CONTEXT(''ID'',''DEPT'') = ''FINANCE'' ');
    END;

Create a policy group named `FINANCE` and add the `FINANCE` policy to the `FINANCE` group:

    BEGIN
    DBMS_RLS.CREATE_POLICY_GROUP('apps','benefit','FINANCE');
    DBMS_RLS.ADD_GROUPED_POLICY('apps','benefit','FINANCE',
    'finance_security','finance', 'security_policy');
    END;

As a result, when the database is accessed, the application initializes the driving context after authentication. For example, with the `HR` application:

    execute apps.security_init.setctx('HR');

### 15.9.4 Validating the Application Used to Connect to the Database

The package implementing the driving context must correctly validate the application that is being used to connect to the database. Although the database always checks the call stack to ensure that the package implementing the driving context sets context attributes, inadequate validation can still occur within the package.

For example, in applications where database users or enterprise users are known to the database, the user needs the `EXECUTE` privilege on the package that sets the driving context. Consider a user who knows that:

- The `BENEFITS` application allows more liberal access than the `HR` application

- The `setctx` procedure (which sets the correct policy group within the driving context) does not perform any validation to determine which application is actually connecting. That is, the procedure does not check either the IP address of the incoming connection (for a three-tier system) or the `proxy_user` attribute of the user session.

Such a user could pass to the driving context package an argument setting the context to the more liberal `BENEFITS` policy group, and then access the `HR` application instead. Because the `setctx` does no further validation of the application, this user bypasses the normally more restrictive HR security policy.

By contrast, if you implement proxy authentication with VPD, then you can determine the identity of the middle tier (and the application) that is actually connecting to the database on behalf of a user. In this way, the correct policy will be applied for each application to mediate data access.

For example, a developer using the proxy authentication feature could determine that the application (the middle tier) connecting to the database is `HRAPPSERVER`. The package that implements the driving context can thus verify whether the `proxy_user` in the user session is `HRAPPSERVER`. If so, then it can set the driving context to use the `HR` policy group. If `proxy_user` is not `HRAPPSERVER`, then it can disallow access.

In this case, when the following query is executed

    SELECT * FROM APPS.BENEFIT;

Oracle Database picks up policies from the default policy group (`SYS_DEFAULT`) and active namespace `HR`. The query is internally rewritten as follows:

    SELECT * FROM APPS.BENEFIT WHERE COMPANY = SYS_CONTEXT('ID','MY_COMPANY') and SYS_CONTEXT('ID','TITLE') = 'MANAGER';

## 15.10 How to Add a Policy to a Table, View, or Synonym

The `DBMS_RLS` package enables you to administer security policies by using its procedures for adding, enabling, refreshing, or dropping policies, policy groups, or application contexts. You need to specify the table, view, or synonym to which you are adding a policy, as well as the data pertinent to that policy, such as the policy name. Such data also includes names for the policy group and the function implementing the policy. You can also specify the types of statements the policy controls (`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE INDEX`, or `ALTER INDEX`). Table 15-2 lists the procedures in the `DBMS_RLS` package.

Table 15-2 DBMS_RLS Procedures

Procedure | Purpose
--- | ---
For Handling Individual Policies |
`DBMS_RLS.ADD_POLICY` | To add a policy to a table, view, or synonym
`DBMS_RLS.ENABLE_POLICY` | To enable (or disable) a policy you previously added to a table, view, or synonym
`DBMS_RLS.REFRESH_POLICY` | To invalidate cursors associated with non-static policies
`DBMS_RLS.DROP_POLICY` | To drop a policy from a table, view, or synonym
For Handling Grouped Policies |
`DBMS_RLS.CREATE_POLICY_GROUP` | To create a policy group
`DBMS_RLS.DELETE_POLICY_GROUP` | To drop a policy group
`DBMS_RLS.ADD_GROUPED_POLICY` | To add a policy to the specified policy group
`DBMS_RLS.ENABLE_GROUPED_POLICY` | To enable a policy within a group
`DBMS_RLS.REFRESH_GROUPED_POLICY` | To reparse the SQL statements associated with a refreshed policy
`DBMS_RLS.DISABLE_GROUPED_POLICY` | To disable a policy within a group
`DBMS_RLS.DROP_GROUPED_POLICY` | To drop a policy which is a member of the specified group
For Handling Application Context |
`DBMS_RLS.ADD_POLICY_CONTEXT` | To add the context for the active application
`DBMS_RLS.DROP_POLICY_CONTEXT` | To drop the context for the application

Alternatively, you can use Oracle Policy Manager to administer security policies.

See Also: PL/SQL Packages and Types Reference for information about using the DBMS_RLS package and all of its procedures and parameters

### 15.10.1 DBMS_RLS.ADD_POLICY Procedure Policy Types

The execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times that policy functions must run, then you can optimize your database server performance. To avoid unnecessary policy function execution, you can choose from five different policy types, which enable you to precisely specify how and how often a policy predicate should change. You can enable these different types of policies, which are listed in Table 15-3, by setting the `policy_type` parameter of the `DBMS_RLS.ADD POLICY` procedure.

Table 15-3 DBMS_RLS.ADD_POLICY Policy Types At a Glance

Policy Types | When Policy Function Executes... | Usage Example | Shared Across Multiple Objects?
--- | --- | --- | ---
`STATIC` | Once, then the predicate is cached in the SGAFoot 1 | View replacement | No
`SHARED_STATIC` | Same as `STATIC` | Hosting environments, such as data warehouses where the same predicate must be applied to multiple database objects | Yes
`CONTEXT_SENSITIVE` | At statement parse time At statement execution time when the local application context has changed since the last use of the cursor | 3-tier, session pooling applications where policies enforce two or more predicates for different users or groups | No
`SHARED_CONTEXT_SENSITIVE` | First time the object is reference in a database session Predicates are cached in the private session memory UGA so policy functions can be shared among objects. | Same as `CONTEXT_SENSITIVE`, but multiple objects can share the policy function from the session UGA | Yes
`DYNAMIC` | Policy function re-executes every time a policy-protected database object is accessed. | Applications where policy predicates must be generated for each query, such as time-dependent policies where users are denied access to database objects at certain times during the day | No

Footnote 1 Each execution of the same cursor could produce a different row set even for the same predicate because the predicate may filter the data differently based on attributes such as `SYS_CONTEXT` or `SYSDATE`.

Static and context sensitive policies enable you to optimize server performance, because they do not run the policy function each time protected database objects are accessed. However, Oracle recommends that before you enable policies as either static or context-sensitive, you first test them as `DYNAMIC` policy types, which run every time. Testing policy functions as `DYNAMIC` policies first enables you to observe how the policy function affects each query, because nothing is cached. This ensures that the functions work properly before you enable them as static or context-sensitive policy types to optimize performance.

Dynamic policies are the system default. If you do not specify a policy type with the `DBMS_RLS.ADD_POLICY` procedure, then by default your policy will be dynamic. You can specifically configure a policy to be dynamic by setting the `policy_type` parameter of the `DBMS_RLS.ADD_POLICY` procedure to `DYNAMIC`. Refer to Example 15-1 for the syntax.

Example 15-1 Syntax for Enabling Policy Types with DBMS_RLS.ADD_POLICY

    DBMS_RLS.ADD_POLICY (

    .
    .
    .
    policy_type => dbms_rls.POLICY_TYPE);

Note: The `DBMS_RLS.ADD POLICY` `policy_type` parameter is intended to replace the `static_policy` parameter, which may be deprecated in future releases.

See Also: The following topics for a more detailed discussion of static and context-sensitive policies: "About Static Policies" "About Context-Sensitive Policies"

### 15.10.2 Optimizing Performance by Enabling Static and Context Sensitive Policies

In previous releases, policies were dynamic, which means that the database runs the policy function for each query or DML statement. In addition to dynamic policies, the current release of Oracle Database provides static and context-sensitive policies. These policy types provide a means to improve server performance, because they do not always rerun policy functions for each DML statement and can be shared across multiple database objects.

Note: When using shared static and shared context-sensitive policies, ensure that the policy predicate does not contain attributes that are specific to a particular database object, such as a column name.

#### 15.10.2.1 About Static Policies

Static policy predicates are cached in SGA, so policy functions do not rerun for each query, resulting in faster performance. When you specify a static policy, the same predicate is always enforced for all users in the instance. However, each execution of the same cursor could produce a different row set even for the same predicate, because the predicate may filter the data differently based on attributes such as `SYS_CONTEXT` or `SYSDATE`.

For example, suppose you enable a policy as either a `STATIC` or `SHARED_STATIC` policy type, which appends the following predicate to all queries made against policy protected database objects:

    WHERE dept=SYS_CONTEXT ('HR_APP','deptno')

Although the predicate does not change for each query, it applies to the query based on session attributes of the `SYS_CONTEXT`. In the case of the preceding example, the predicate would return only those rows where the department number matches the `deptno` attribute of the `SYS_CONTEXT`, which would be the department number of the user who is querying the policy-protected database object.

You can enable static policies by setting the `policy_type` parameter of the `DBMS_RLS.ADD_POLICY` procedure to either `STATIC` or `SHARED_STATIC`, depending on whether or not you want the policy to be shared across multiple objects. (Refer to Example 15-1 for the syntax.)

##### 15.10.2.1.1 When to Use Static Policies

Static policies are ideal for environments where every query requires the same predicate and fast performance is essential, such as hosting environments. For these situations when the policy function appends the same predicate to every query, rerunning the policy function each time adds unnecessary overhead to the system. For example, consider a data warehouse that contains market research data for customer organizations that are competitors of each other. The warehouse must enforce the policy that each organization can see only their own market research, which is expressed by the predicate `WHERE subscriber_id=SYS_CONTEXT('customer', 'cust_num')`. Using `SYS_CONTEXT` for the application context enables the database to dynamically change the rows that are returned. There is no need to rerun the function, so the predicate can be cached in the SGA, thus conserving system resources and improving performance.

#### 15.10.2.2 About Context-Sensitive Policies

In contrast to static policies, context-sensitive policies do not always cache the predicate. With context-sensitive policies, the server assumes that the predicate will change after statement parse time. But if there is no change in local application context, the server does not rerun the policy function within the user session. If there has been a change in context, then the server reruns the policy function to ensure that it captures any changes to the predicate since the initial parsing. These policies are useful where different predicates should apply depending on which user is executing the query. For example, consider the case where managers should always have the predicate `WHERE group=managers` and employees should always have the predicate `WHERE empno=emp_id`.

Shared context-sensitive policies operate in the same way as regular context-sensitive policies, except they can be shared across multiple database objects. For this policy type, all objects can share the policy function from the UGA, where the predicate is cached until the local session context changes.

You can enable context-sensitive policies by setting the `policy_type` parameter of the `DBMS_RLS.ADD_POLICY` procedure to either `CONTEXT_SENSITIVE` or `SHARED_CONTEXT_SENSITIVE`. (Refer to Example 15-1 for the syntax.)

##### 15.10.2.2.1 When to Use Context-Sensitive Policies

This type of policy is useful when a predicate need not change for a user session, but the policy must enforce two or more different predicates for different users or groups. For example, consider a `SALES_HISTORY` table with a single policy, which states that analysts can see only their own products and regional employees can see only their own region. In this case, the server must rerun the policy function each time the type of user changes. The performance gain is realized when a user can log in and issue several DML statements against the protected object without causing the server to rerun the policy function.

Note: For session pooling where multiple clients share a database session, the middle tier must reset the context during client switches.

### 15.10.3 Adding Policies for Column-Level VPD

Column-level VPD, which can be applied to a table or a view, enables you to enforce security when a security-relevant column is referenced in a query, resulting in row-level security. Column-level VPD cannot be applied to a synonym.

It can be configured to produce two distinct behaviors as follows:

- Default Behavior

- Column-masking Behavior

The following example shows a VPD policy in which sales department users should not see the salaries of people outside their own department (department number 30). The relevant columns for such a policy are `SAL` and `COMM`. First, the VPD policy function is created and then added by using the `DBMS_RLS` PL/SQL package as shown in Example 15-2.

Example 15-2 Creating and Adding a Column-Level VPD Policy

    */Create a policy function which does not expose salaries of employees outside the sales department (department 30)/*

    CREATE OR REPLACE FUNCTION pf1 (oowner IN VARCHAR2, ojname IN VARCHAR2)
    RETURN VARCHAR2 AS
    con VARCHAR2 (200);
    BEGIN
       con := 'deptno=30';
       RETURN (con);
    END pf1;

Then the policy is added with the `DBMS_RLS` package as follows:

    BEGIN
       DBMS_RLS.ADD_POLICY (object_schema=>'scott', object_name=>'emp',
                            policy_name=>'sp', function_schema=>'pol_admin',
                            policy_function=>'pf1',
                            sec_relevant_cols=>'sal,comm');
    END;

The different behaviors of column-level VPD are discussed in the following sections using Example 15-2 as a starting point for discussion.

#### 15.10.3.1 Default Behavior

The default behavior for column-level VPD is to restrict the number of rows returned for a query that references columns containing sensitive information. These security-relevant columns are specified with the `sec_relevant_cols` parameter of the `DBMS_RLS.ADD_POLICY` procedure.

For an example of column-level VPD default behavior, consider sales department users with `SELECT` privilege on the `emp` table, which is protected with the column-level VPD policy created in Example 15-2. When users run the following query:

    SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
    WHERE d.deptno = e.deptno;

the database returns a subset of rows as follows:

    ENAME          DNAME          JOB                   SAL          COMM
    -------------- -------------- ------------ ------------ -------------
    ALLEN          SALES          SALESMAN             1600           300
    WARD           SALES          SALESMAN             1250           500
    MARTIN         SALES          SALESMAN             1250          1400
    BLAKE          SALES          MANAGER              2850
    TURNER         SALES          SALESMAN             1500             0
    JAMES          SALES          CLERK                 950

Only those rows are displayed in which the user should have access to all columns.

#### 15.10.3.2 Column-masking Behavior

In contrast to the default behavior of column-level VPD, column-masking displays all rows, but returns sensitive column values as `NULL`. To include column-masking in your policy, set the `sec_relevant_cols_opt` parameter of the `DBMS_RLS.ADD_POLICY` procedure to `dbms_rls.ALL_ROWS`. Also set the default behavior parameter.

Example 15-3 shows column-level VPD column-masking. It uses the same VPD policy as Example 15-2 but with `sec_relevant_cols_opt` specified as `dbms_rls.ALL_ROWS`.

Example 15-3 Adding a Column-level VPD Policy with Column-masking Behavior

    */add the ALL_ROWS policy/*
    BEGIN
         DBMS_RLS.ADD_POLICY(object_schema=>'scott', object_name=>'emp',
                             policy_name=>'sp', function_schema=>'pol_admin',
                             policy_function=>'pf1',
                             sec_relevant_cols=>'sal,comm',
                             sec_relevant_cols_opt=>dbms_rls.ALL_ROWS);
    END;

Assume that a sales department user with `SELECT` privilege on the emp table runs the following query:

-     SELECT ENAME, d.dname, JOB, SAL, COMM from emp e, dept d
      WHERE d.deptno = e.deptno;

The database returns all rows specified in the query, but with certain values masked because of the VPD policy:

    ENAME          DNAME          JOB                   SAL          COMM
    -------------- -------------- ------------ ------------ -------------
    SMITH          RESEARCH       CLERK
    ALLEN          SALES          SALESMAN             1600           300
    WARD           SALES          SALESMAN             1250           500
    JONES          RESEARCH       MANAGER
    MARTIN         SALES          SALESMAN             1250          1400
    BLAKE          SALES          MANAGER              2850
    CLARK          ACCOUNTING     MANAGER
    SCOTT          RESEARCH       ANALYST
    KING           ACCOUNTING     PRESIDENT
    TURNER         SALES          SALESMAN             1500             0
    ADAMS          RESEARCH       CLERK
    JAMES          SALES          CLERK                 950
    FORD           RESEARCH       ANALYST
    MILLER         ACCOUNTING     CLERK

Note that column-masking has returned all rows requested by the sales user query, but has made the `SAL` and `COMM` columns `NULL` for employees outside the sales department.

The following considerations apply to column-masking:

- Column-masking applies only to `SELECT` statements.

- Column-masking conditions generated by the policy function must be a simple Boolean expressions, unlike regular VPD predicates.

- For applications that perform calculations, or do not expect `NULL` values, use standard column-level VPD, specifying `sec_relevant_cols` rather than column-masking.

- Column-masking used with `UPDATE AS SELECT` will update only the columns that users are allowed to see.

- For some queries, column-masking may prevent some rows from displaying. For example:

  -     SELECT * FROM employees
        WHERE salary = 10

  This query may not return rows if the `salary` column returns a `NULL` value, because the column-masking option has been set.

See Also: The chapter on the `DBMS_RLS` package in PL/SQL Packages and Types Reference for a discussion of parameters and usage examples for the `DBMS_RLS.ADD_POLICY` procedure

### 15.10.4 Enforcing VPD Policies on Specific SQL Statement Types

VPD policies can be enforced for `SELECT`, `INSERT`, `UPDATE`, `INDEX`, and `DELETE` statements. Specify any combination of these statement types with the `DBMS_RLS.ADD_POLICY` procedure `statement_types` parameter as follows:

    DBMS_RLS.ADD_POLICY (

    .
    .
    .

    statement_types=>'SELECT,INDEX');

#### 15.10.4.1 Enforcing Policies on Index Maintenance

A user who has privileges to maintain an index can see all the row data even if the user does not have full table access under a regular query, such as `SELECT`. For example, a user can create a function-based index that contains a user-defined function with column values as its arguments. During index creation, the server passes column values of every row into the user function, making the row data available to the user who creates the index. Administrators can enforce VPD policies on index maintenance operations by specifying `INDEX` with the `statement_types` parameter as shown in the previous section.

## 15.11 How to Check for Policies Applied to a SQL Statement

`V$VPD_POLICY` allows one to perform a dynamic view in order to check what policies are being applied to a SQL statement. When debugging, in your attempt to find which policy corresponds to a particular SQL statement, you should use the following table.

Table 15-4 V$VPD_POLICY

Column Name | Type
--- | ---
`ADDRESS` | `RAW(4|8)`
`PARADDR` | `RAW(4|8)`
`SQL_HASH` | `NUMBER`
`SQL_ID` | `VARCHAR2(13)`
`CHILD_NUMBER` | `NUMBER`
`OBJECT_OWNER` | `VARCHAR2(30)`
`OBJECT_NAME` | `VARCHAR2(30)`
`POLICY_GROUP` | `VARCHAR2(30)`
`POLICY` | `VARCHAR2(30)`
`POLICY_FUNCTION_OWNER` | `VARCHAR2(30)`
`PREDICATE` | `VARCHAR2(4000)`
`DBMS_RLS.REFRESH_GROUPED_POLICY` | `VARCHAR2(4096)`

See Also: Oracle Database Reference for more information about the `V$VPD_POLICY` view

## 15.12 Users Exempt from VPD Policies

Two classes of users are exempt from VPD policies: the `SYS` user is exempt by default, and any other user can be exempt if granted the `EXEMPT ACCESS POLICY` system privilege. These two cases are discussed in the following sections.

### 15.12.1 SYS User Exempted from VPD Policies

The database user `SYS` is always exempt from VPD or Oracle Label Security policy enforcement, regardless of the export mode, application, or utility that is used to extract data from the database. However, `SYSDBA` actions can be audited.

### 15.12.2 EXEMPT ACCESS POLICY System Privilege

The system privilege `EXEMPT ACCESS POLICY` allows a user to be exempted from all fine-grained access control policies on any `SELECT` or DML operation (`INSERT`, `UPDATE`, and `DELETE`). This provides ease of use for administrative activities such as installation and import and export of the database through a non-`SYS` schema.

Also, regardless of the utility or application that is being used, if a user is granted the `EXEMPT ACCESS POLICY` privilege, then the user is exempt from VPD and Oracle Label Security policy enforcement. That is, the user will not have any VPD or Oracle Label Security policies applied to their data access.

Because `EXEMPT ACCESS POLICY` negates the effect of fine-grained access control, this privilege should only be granted to users who have legitimate reasons for bypassing fine-grained access control enforcement. This privilege should not be granted `WITH ADMIN OPTION`, so that users cannot pass on the `EXEMPT ACCESS POLICY` privilege to other users, and thus propagate the ability to bypass fine-grained access control.

## 15.13 Automatic Reparse

Note: This feature is applicable when `COMPATIBLE` is set to 9.0.1.

Starting from Oracle9i, queries against objects enabled with fine-grained access control always run the policy function to make sure that the most current predicate is used for each policy. For example, in the case of a time-based policy function, in which queries are only allowed between 8:00 a.m. and 5:00 p.m., a cursor execution parsed at noon cause the policy function to run, ensuring that the policy is consulted again for the query.

Automatic reparse does not occur under the following conditions:

- When you specify the `STATIC_POLICY=TRUE` while adding the policy to indicate that the policy function always returns the same predicate.

- When you set the `_dynamic_rls_policies` parameter to `FALSE` in the initialization parameters files. Typically, this parameter is set to `FALSE` for users whose security policies do not return different predicates within a database session to reduce the execution overhead.

For deployment environments where the latest application context value is always the desired value, the `_app_ctx_vers` parameter can be set to `FALSE` in the initialization parameters file to reduce the overhead of application context scoping. By default, it is set to `TRUE` and changes of value within a SQL statement are not visible. This default may change in the future, thus developers should be careful not to allow changes of application context values within a SQL statement using a user-defined function. In general, you should not depend on the sequence of SQL statement execution, which can yield inconsistent results depending on query plans.

See Also: "Using Dynamic SQL with SYS_CONTEXT"

## 15.14 VPD Policies and Flashback Query

By default, operations on the database use the most recent committed data available. The flashback query feature enables you to query the database as it was at some time in the past. To write an application that uses flashback query, you can use the `AS OF` clause in SQL queries to specify either a time or a system change number (SCN) and then query against the committed data from the specified time. You can also use the `DBMS_FLASHBACK` PL/SQL package, which requires more code, but enables you to perform multiple operations, all of which refer to the same past time.

Flashback queries return data as it stood at the time specified in the query. However, if you use flashback query against a database object that is protected with VPD policies, then the current policies are applied to the old data. Applying the current VPD policies to flashback query data is more secure because it reflects the most current business policy.

See Also: Oracle Database Application Developer's Guide - Fundamentals for more information about the flashback query feature and how to write applications that use it PL/SQL Packages and Types Reference for more information about the `DBMS_FLASHBACK` PL/SQL package